package com.ysd.repository;

import java.util.List;

import javax.transaction.Transactional;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.ysd.entity.Member;

public interface MemberRepository extends JpaRepository<Member, Integer>,JpaSpecificationExecutor<Member> {

	//修改状态(状态修改成资源室Id)
	@Transactional
	@Modifying
	@Query("update Member set status=?1 where id=?2")
	int updateStatusInById(String status, int id);
	
	//修改状态(状态修改成0)
	@Transactional
	@Modifying
	@Query("update Member set status=0 where id=?1")
	int updateStatusOutById( int id);
	
	//修改数据
	@Transactional
	@Modifying
	@Query("update Member set name=?2,sex=?3,category=?4,department=?5,profession=?6,grade=?7,phone=?8,remark=?9 where id=?1")
	int updateMember(int id,String name,String sex,Integer category,String department,String profession,String grade,String phone,String remark);

	//通过学号查询
	@Query(value = "select m.* from Member m where m.card_no=?1",nativeQuery = true)
	Member selMemberByCard_no(String card_no);
	
	//通过学号查询
	@Query(value = "select m.* from Member m where m.card=?1",nativeQuery = true)
	Member selMemberByCard(String card);
	
	//删除多条
	@Transactional 
	@Modifying 
	@Query(value = "delete from member where id in(?1)", nativeQuery = true)
	public int deleMemberByIds(List ids); 
	
	//添加会员excel导入
	@Transactional 
	@Modifying 
	@Query(value = "insert into member (id,card_no,name,sex,category,card,department,profession,grade,phone,regdate,status) values (:#{#mb.id},:#{#mb.card_no},:#{#mb.name},:#{#mb.sex},:#{#mb.category},:#{#mb.card},:#{#mb.department},:#{#mb.profession},:#{#mb.grade},:#{#mb.phone},:#{#mb.regdate},:#{#mb.status})", nativeQuery = true)
	public int InsertMember(@Param("mb")Member mb);
}
